﻿using System.Diagnostics;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;
using System.Windows.Forms;


namespace BierdopjeTV.Database
{
      class Paths
      {
          public int id;
          public string path;
      }

      class Replacemens
      {
          public int id;
          public string ShowNameOld;
          public string ShowNameNew;
      }


    static class Database
    {
        
        static public List<Paths> ImportPaths(string actie, int id, string path)
        {
            List<Paths> _List = new List<Paths>();
            Paths _Path;
            
           
            SQLiteResultSet _ResultSet;

            switch (actie)
            {
                case "Opvragen":
                    //request all importpaths from the database
                    _ResultSet = Helpers.SqliteHelper.ExecuteQuery("SELECT ImportPathID, ImportPath FROM ImportPaths");

                    if (_ResultSet.Rows.Count > 0)
                    {
                        for (int i = 0; i < _ResultSet.Rows.Count; i++)
                        {

                            _Path = new Paths();
                            _Path.id = Convert.ToInt32(_ResultSet.Rows[i].fields[0]);
                            _Path.path = _ResultSet.Rows[i].fields[1];

                            _List.Add(_Path);
                            _Path = null;

                        }
                    }
                    break;

                case "Bijwerken":
                    //update the database
                    Helpers.SqliteHelper.ExecuteQuery("UPDATE ImportPaths SET ImportPath = '" + path.Replace("'", "\'") + "' WHERE ImportPathID = " + id + ";");
                    break;
                case "Verwijderen":
                    //Delete from database
                    Helpers.SqliteHelper.ExecuteQuery("DELETE FROM ImportPaths WHERE ImportPathID = " + id + ";");
                    break;
                case "Toevoegen":
                    //Add to the database
                    Helpers.SqliteHelper.ExecuteQuery("INSERT INTO ImportPaths (ImportPath) Values ('" + path.Replace("'", "\'") + "')");
                                        
                    break;
            }
            return _List;
        }

        static public List<Replacemens> ReplaceMents(string actie, int id, string oud, string nieuw)
        {
            List<Replacemens> _List = new List<Replacemens>();
            Replacemens _Replacement;

         
            SQLiteResultSet _ResultSet;

            switch (actie)
            {
                case "Opvragen":
                    //request all importpaths from the database
                    _ResultSet = Helpers.SqliteHelper.ExecuteQuery("SELECT ReplaceMentID, ShowNameOld, ShowNameNew FROM ReplaceMents");

                    if (_ResultSet.Rows.Count > 0)
                    {
                        for (int i = 0; i < _ResultSet.Rows.Count; i++)
                        {

                            _Replacement = new Replacemens();
                            _Replacement.id = Convert.ToInt32(_ResultSet.Rows[i].fields[0]);
                            _Replacement.ShowNameOld = _ResultSet.Rows[i].fields[1];
                            _Replacement.ShowNameNew = _ResultSet.Rows[i].fields[2];
                            

                            _List.Add(_Replacement);
                            _Replacement = null;

                        }
                    }
                    break;

                case "Bijwerken":
                    //update the database
                    Helpers.SqliteHelper.ExecuteQuery("UPDATE ReplaceMents SET ShowNameOld = '" + oud.Replace("'", "\'") + "', ShowNameNew = '" + nieuw.Replace("'", "\'") + "' WHERE ReplaceMentID = " + id + ";");
                    break;
                case "Verwijderen":
                    //Delete from database
                    Helpers.SqliteHelper.ExecuteQuery("DELETE FROM ReplaceMents WHERE ReplaceMentID = " + id + ";");
                    break;
                case "Toevoegen":
                    //Add to the database
                    Helpers.SqliteHelper.ExecuteQuery("INSERT INTO ReplaceMents (ShowNameOld,ShowNameNew) Values ('" + oud.Replace("'", "\'") + "', '" + nieuw.Replace("'", "\'") + "')");

                    break;
            }
            return _List;
        }

        static public void UpdateSetting(string SettingName, string SettingValue)
        {
            Helpers.Logger.Write("UPDATE Settings SET SettingValue = '" + SettingValue.Replace("'", "\'") + "' WHERE SettingName = '" + SettingName + "';", "Database.UpdateSetting()");
            Helpers.SqliteHelper.ExecuteQuery("UPDATE Settings SET SettingValue = '" + SettingValue.Replace("'", "\'") + "' WHERE SettingName = '" + SettingName + "';");            
        }

        static public string getSettingValue(string SettingName)
        {

            SQLiteResultSet _ResultSet;
            string _SettingValue = "";

            _ResultSet = Helpers.SqliteHelper.ExecuteQuery("SELECT SettingValue FROM Settings WHERE SettingName = '" + SettingName + "';");

            if (_ResultSet.Rows.Count > 0)
            {
                _SettingValue = _ResultSet.Rows[0].fields[0].ToString();
            }
            else
            {
                //add the setting
                Helpers.SqliteHelper.ExecuteQuery("INSERT INTO Settings(SettingName) VALUES ('" + SettingName + "')");
            }
             return _SettingValue;
                            
        }

        static public List<string> GetImportPaths()
        {
            List<string> _ImportPaths = new List<string>();
            SQLiteResultSet _Results = new SQLiteResultSet();

            _Results = Helpers.SqliteHelper.ExecuteQuery("SELECT ImportPath FROM ImportPaths");

            for (int i = 0; i < _Results.Rows.Count; i++)
            {
                _ImportPaths.Add(_Results.Rows[i].fields[0].ToString());
                
            }

            return _ImportPaths;
            
        }

        static public void MarkAsWatched(int EpisodeID)
        {
            Helpers.Logger.WriteSimpel("Marking Episode " + EpisodeID.ToString() + " to wachted...");
            Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Watched = 1 WHERE EpisodeID = " + EpisodeID.ToString());
        }

    }
}
